JSONL ファイルをもとに Amazon Athena で INSERT INTO してみる

#Amazon Athena

よしなな

2024.04.11

こんにちは！よしななです。
今回は、前回の投稿で作成したjsonl_test_tableをもとに新規作成したjsonl_test_table_insertに、INSERT INTOを使用してデータを追加してみます。

やりたいこと

前回の投稿で作成したjsonl_test_tableをもとに新規作成したjsonl_test_table_insertに、INSERT INTOを実行してデータを追加するところまでを実施します。
元テーブルのうち、GenderカラムがFemaleのレコード（行）をjsonl_test_table_insertに追加するクエリを作成し、実行するところまでを試します。

事前準備

前回の記事の事前準備と同様に、以下の5点を実施します。

手順

INSERT INTO 用テーブルの作成

事前準備が完了したら、INSERT INTO用のテーブルを作成していきます。
以下のCREATE TABLEクエリを実行し、空のjsonl_test_table_insertテーブルを作成します。

CREATE EXTERNAL TABLE IF NOT EXISTS jsonl_test_table_insert(
    `no` int, 
    `name` string, 
    `gender` string, 
    `grades` struct<japanese:int,math:int,english:int,science:int,socialstudies:int>)
ROW FORMAT SERDE 
    'org.apache.hive.hcatalog.data.JsonSerDe' 
LOCATION
    's3://jsonl-test/empty/'

CREATE TABLE クエリ構文について

1.org.apache.hive.hcatalog.data.JsonSerDe について
AWS 公式ドキュメントによると、Amazon Athena でINSERT INTOする場合、ROW FORMAT SERDE org.openx.data.jsonserde.JsonSerDeはサポート外となっているため、jsonl_test_table_insertテーブル作成時は ROW FORMAT SERDE をorg.apache.hive.hcatalog.data.JsonSerDeに設定する必要があります。

参考公式ドキュメント：https://docs.aws.amazon.com/ja_jp/athena/latest/ug/insert-into.html#insert-into-limitations

2.空テーブル作成について
データを含まないテーブルを作成したい場合、LOCATIONで指定する S3 バケットを空にしておきます。今回は S3 バケット/jsonl-test配下に/emptyフォルダを作成しました。

こちらでjsonl_test_table_insertテーブルが作成できたので、INSERT INTOをしてデータを追加していきます。

INSERT INTO の実行

それでは、INSERT INTOでデータを追加していきます。
元テーブルのうち、GenderカラムがFemaleのレコード（行）をjsonl_test_table_insertに追加します。以下のクエリを実行します。

INSERT INTO "jsonl_test_table_insert"
SELECT
    "no",
    "name",
    "gender",
    "grades"
FROM "jsonl_test"."jsonl_test_table"
WHERE gender='Female';

INSERT INTO クエリ構文について

1.INSERT INTO

INSERT INTO テーブル名でデータを登録するテーブル名を指定します。

2.SELECT FROM

テーブルに追加するカラムを指定します。
SELECTクエリがテーブル列を指定する場会、カラム名の順番を一致させる必要があります。
今回は、no,name,gender,gradesの順で指定します。

3.WHERE

こちらで、追加するテーブルの条件を指定しています。
元テーブルのうち、GenderカラムがFemaleのレコード（行）をjsonl_test_table_insertに追加したいので、gender='Female'を指定します。

実行結果の確認

INSERT INTOクエリが実行できたので、以下のコードを実行しテーブルの中身を確認してみます。

SELECT * FROM "jsonl_test"."jsonl_test_table_insert" limit 10;

jsonl_test_table_insertテーブルに、GenderがFemaleのカラムのみが追加されたのを確認できました！以上で、JSONL ファイルをもとに Amazon Athena でINSERT INTOしてみるは完了となります。
ここまで読んでいただきありがとうございました！